package com.image;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ImageDAO {
	private  Connection conn = null;
	
	public ImageDAO(Connection conn) {
		this.conn = conn;
	}
	
	public int getMaxNum() {
		Statement stmt = null;
		ResultSet rs = null;
		
		int maxNum = 0;
		
		String sql = "SELECT NVL(MAX(num), 0) FROM imageTest";
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			if(rs.next())
				maxNum = rs.getInt(1);
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
		
		return maxNum;
	}
	
	public int insertData(ImageDTO dto) {
		PreparedStatement pstmt = null;
		int result = 0;
		
		String sql = "INSERT INTO imageTest(num, subject, saveFileName) ";
		sql += " VALUES (?, ?, ?)";
		
		try {
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, dto.getNum());
			pstmt.setString(2, dto.getSubject());
			pstmt.setString(3, dto.getSaveFileName());
			
			result = pstmt.executeUpdate();
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
		
		return result;
	}
	
	public List<ImageDTO> getListData(int start, int end) {
		List<ImageDTO> lists = new ArrayList<ImageDTO>();
		
		Statement stmt = null;
		ResultSet rs = null;
		String sql;
		
		sql = "SELECT * FROM ( ";
		sql += "SELECT ROWNUM rnum, data.* FROM ( ";
		sql += "SELECT num, subject, saveFileName ";
		sql += " FROM imageTest ORDER BY num DESC) data )";
		sql += " WHERE rnum >= " + start + " AND  rnum <= " + end;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				ImageDTO dto = new ImageDTO();
				
				dto.setNum(rs.getInt("num"));
				dto.setSubject(rs.getString("subject"));
				dto.setSaveFileName(rs.getString("saveFileName"));

				lists.add(dto);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
		
		return lists;
	}
	
	public int getDataCount() {
		Statement stmt = null;
		ResultSet rs = null;
		
		int total_count = 0;
		
		String sql = "SELECT NVL(COUNT(num), 0) FROM imageTest";
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			if(rs.next())
				total_count = rs.getInt(1);
			
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
		
		return total_count;
	}

	public ImageDTO getReadData(int num) {
		ImageDTO dto = new ImageDTO();

		Statement stmt = null;
		ResultSet rs = null;

		try {
			stmt = conn.createStatement();
			
			String fields = "num, subject, saveFileName ";
			String sql = "SELECT " + fields + " FROM imageTest WHERE num = " + num;
	
			rs = stmt.executeQuery(sql);
			
			if(rs.next()) {
				dto.setNum(rs.getInt("num"));
				dto.setSubject(rs.getString("subject"));
				dto.setSaveFileName(rs.getString("saveFileName"));
			}
			
			rs.close();
			stmt.close();
		}catch(SQLException e) {
			System.out.println(e.toString());
		}

		return dto;
	}
	
	public int deleteData(int num) {
		int result = 0;
		Statement stmt = null;
		
		String sql = "DELETE imageTest WHERE num = " + num;
		
		try {
			stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
			stmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}

		return result;
	}
}
